



ALTER                  proc sp_create_update_assembly_release
 
 @processStep_id bigint,
 @assembly_release_id int,
 @status_id int,
 @po_number varchar(50),
 @location_id int,
 @release_date datetime,
 @bonding_diagram varchar(50), 
 @pn_marking varchar(50), 
 @package varchar(50), 
 @category varchar(50), 
 @build_instruction varchar(50), 
 @special_instructions varchar(2000),
 @from_location_id varchar(100),
 @fuse_file_location varchar(50),
 @wafer_map_location varchar(50),
 @line_1 varchar(50),
 @line_2 varchar(50),
 @line_3 varchar(50),
 @line_4 varchar(50),
 @line_5 varchar(50),
 @line_6 varchar(50),
 @mark_at_assembly varchar(50),
 @mark_at_test varchar(50),
 @temp_assembly_release_id int OUTPUT -- This is the output parameter.
 
 as 
 begin
 
 declare @temp_product_id int
 declare @temp_base_product_name varchar(50)
 declare @temp_processStep_id bigint
 declare @temp_ps_type varchar(20)
 declare @temp_ps_type_name varchar(20)
 declare @temp_assembly_release_status varchar(50)
 declare @temp_assmbly_rel_staus_Id int
 declare @temp_location_id int

 select @temp_assembly_release_status = assembly_release_status from status_table where status_id = @status_id 
 select @temp_product_id = product_id, @temp_base_product_name = part_number, @temp_processStep_id = processStep_id  from product where processstep_id = @processStep_id
 select @temp_ps_type = ps_type, @temp_ps_type_name = ps_type_name from process_step where processstep_id = @processStep_id

 	if @assembly_release_id = -1
 	begin
 		select @temp_assmbly_rel_staus_Id = status_id from status_table where assembly_release_status = 'Issued'
 		insert into assembly_release_table 
 		( Release_Date, Product_Id, BaseProduct_name, PO_Number, Status_ID, Location_ID, Bonding_Diagram, 
 		PN_Marking, Package, Category, Build_Instruction, Special_Instructions, vendor_id, Fuse_File_Location, 
 		Wafer_Map_Location, Line_1, Line_2, Line_3, Line_4, Line_5, Line_6, Mark_At_Assembly, Mark_At_Test )
 		values
 		( @release_date, @temp_product_id, @temp_base_product_name, @po_number, @temp_assmbly_rel_staus_Id, 
 		@location_id, @bonding_diagram, @pn_marking, @package, @category, @build_instruction, 
 		@special_instructions, @from_location_id, @fuse_file_location, @wafer_map_location, @line_1, 
 		@line_2, @line_3, @line_4, @line_5, @line_6, @mark_at_assembly, @mark_at_test )
 		set @temp_assembly_release_id = (select @@IDENTITY)
 	end
 
 	else if @temp_assembly_release_status = 'Issued'
 	begin
 		update assembly_release_table set Release_Date = @release_date, PO_Number = @po_number, Bonding_Diagram = @bonding_diagram, 
 		PN_Marking = @pn_marking, Package = @package, Category = @category, Build_Instruction = @build_instruction, 
 		Special_Instructions = @special_instructions, Fuse_File_Location = @fuse_file_location, 
 		Wafer_Map_Location = @wafer_map_location, Line_1 = @line_1, Line_2 = @line_2, Line_3 = @line_3, 
 		Line_4 = @line_4, Line_5 = @line_5, Line_6 = @line_6, Mark_At_Assembly = @mark_at_assembly, Mark_At_Test = @mark_at_test
 		where AssemblyRelease_ID = @assembly_release_id
 		set @temp_assembly_release_id = @assembly_release_id
 	end
 
 	select @temp_assembly_release_id
 	return
 end



